package ca.ualberta.cs.CLRDB;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;

import ca.ualberta.cs.CLRDB.interfaces.EntityRelManagement;
import ca.ualberta.cs.CLRDB.interfaces.RateAccess;

import uk.ac.liv.c3connector.AnnotationModel;

public class RateManagement extends EntityRelManagement implements RateAccess{
	
	private static ArrayList<String> rateResDBFields = new ArrayList<String>();
	
	public RateManagement(){
		mainTableName = "RateResource";
		
		rateResDBFields.clear();
		rateResDBFields.add("clarity"); rateResDBFields.add("structure"); 
		rateResDBFields.add("novelty"); rateResDBFields.add("usefulness");
		rateResDBFields.add("total");
	}

public void initialize(Statement statement) throws SQLException{
		
	/*try{
		statement.executeUpdate("DROP TABLE Rating");
	}catch(Exception e){}*/
	
	try{
		statement.executeUpdate("DROP TABLE RateResource");
		statement.executeUpdate("DROP TABLE RateAnnotation");
	}catch(Exception e){}
	
	statement.executeUpdate("CREATE TABLE RateResource ( " +
			"resourceId INTEGER NOT NULL, " +
			"personId INTEGER NOT NULL, " +
			"clarity SMALLINT, " +
			"structure SMALLINT, " +
			"novelty SMALLINT, " +
			"usefulness SMALLINT, " +
			"total SMALLINT, " +
			"date Date, "+ 
			"CONSTRAINT FK_personid FOREIGN KEY (personId) " +
            	"REFERENCES Person (ID) ON DELETE CASCADE, " +
            "CONSTRAINT FK_resourceid FOREIGN KEY (resourceId) " +
                "REFERENCES Resource (ID) ON DELETE CASCADE, " +
            "CONSTRAINT PK_resource_user PRIMARY KEY (resourceId, personId) " +                        
	") IN CLRSPACE");
		numOfCols = 8;
		
		statement.executeUpdate("CREATE TABLE RateAnnotation ( " +				
				"annotationId INTEGER NOT NULL, " +
				"personId INTEGER NOT NULL, " +
				"value CHAR(1) NOT NULL, " +
				"date Date, "+ 
				"CONSTRAINT FK_personid FOREIGN KEY (personId) " +
	            	"REFERENCES Person (ID) ON DELETE CASCADE, " +
	            "CONSTRAINT FK_annotationid FOREIGN KEY (annotationId) " +
	                "REFERENCES Annotation (ID) ON DELETE CASCADE, " +
	            "CONSTRAINT val CHECK (value = 'u' OR value = 'd'), " +
	            "CONSTRAINT PK_annotation_user PRIMARY KEY (annotationId, personId) " +  
		") IN CLRSPACE");
				
	}

	public void add(){
		
	}
	
	public void updateRateResource(AnnotationModel am, String username){
		
		Connection con = DatabaseController.connectToDB();
	   	Statement statement;
		
	   	try{
			Integer resourceId = -1;
			Integer personId = -1;
			
			statement = con.createStatement();
			
			if(am.getResourceId() != null ){
				resourceId = am.getResourceId(); //already exists (may bib have been set or not, anyway it doesn't change in time of publishing annos!)				
				//TODO but I have not thought about copy to server
				DatabaseController.resourceMgmt.updateResourceDigest(resourceId, am.getDocumentDigest(),am.getDocumentTextDigest(),con);
			}
			else{
				resourceId = DatabaseController.resourceMgmt.resourceExists(am.resourceUri, con);
				if(resourceId == -1) //not exists
					resourceId = DatabaseController.resourceMgmt.addResource(am.resourceUri,am.getDocumentDigest(),am.getDocumentTextDigest(),con);
			}
			
			ResultSet resU = statement.executeQuery("SELECT ID FROM Person WHERE username = '" + username + "'");
			resU.next();
			personId = resU.getInt("ID");
			
			//check if already there
			
			ResultSet res = statement.executeQuery("SELECT * FROM RateResource WHERE " +
					"resourceId = "+resourceId +" AND personId = "+personId);
			
			boolean exists = false;
			if(res.next())
				exists = true;
			
			HashMap<String,String> attr = ContentSerializer.parseAttributes(am.getAnnotationBody());
			
			String queryKeysBase = "resourceId,personId,";
			String queryValsBase = resourceId +","+personId+",";
			
			String queryKeys = "date,";
			String queryVals = "'" + (new java.sql.Date(new Date().getTime())).toString() +"',";
			
			for(String col : rateResDBFields ){
				if( attr.get(col) != null ){
					try{						
						queryVals += Integer.parseInt(attr.get(col))+",";
						queryKeys += col +",";
					}catch(Exception e){ e.printStackTrace(); }					
				}
			}
			
			queryKeys = queryKeys.substring(0, queryKeys.length()-1);
			queryVals = queryVals.substring(0, queryVals.length()-1);
			
			if(exists){
				statement.executeUpdate("UPDATE RateResource SET ("+queryKeys+")" +
						" = ("+queryVals+") WHERE resourceId = "+resourceId+
						" AND personId = "+personId);
			}
			else
				statement.executeUpdate("INSERT INTO RateResource ("+queryKeysBase+queryKeys+") VALUES ("+queryValsBase+queryVals+")");
			
			statement.close();
			con.commit();
			
	   	} catch (SQLException e) {
			System.err.println("SQLException:");
			while(e!=null) {
				System.err.println ("Error msg: " + e.getMessage());
				System.err.println ("SQLSTATE: " + e.getSQLState());
				System.err.println ("Error code: " + e.getErrorCode());
				e.printStackTrace();
				//if(e.getErrorCode() == -4470 )
				//FIXME I think I should restart db2 driver?
				e = e.getNextException();
			}				
		} catch (Exception e) {			
			e.printStackTrace();			
		} 
	}
	
	public HashMap<String,String> getCurrentRateResource(String username, Integer resourceId, String resourceUri){
		HashMap<String,String> attr = null;
		
		try{
			Connection con = DatabaseController.connectToDB();
		   	Statement statement = con.createStatement();
		   			   	
			Integer personId = -1;
			
			if(resourceId == null || resourceId == -1 ){														
				resourceId = DatabaseController.resourceMgmt.resourceExists(resourceUri, con);
				if(resourceId == -1) //not exists
					resourceId = DatabaseController.resourceMgmt.addResource(resourceUri,null,null,con);
			}
			
			ResultSet resU = statement.executeQuery("SELECT ID FROM Person WHERE username = '" + username + "'");
			resU.next();
			personId = resU.getInt("ID");
		   	
		   	ResultSet res = statement.executeQuery("SELECT * FROM RateResource WHERE resourceId = "+resourceId+" AND personId = "+personId);
		   	
		   	if(res.next()){		
		   		attr = new HashMap<String, String>();
		   		for(String col : rateResDBFields){
		   			attr.put(col, String.valueOf(res.getInt(col)));
		   		}
		   	}
		   	
		   	statement.close();
			con.commit();
			
		} catch (SQLException e) {
			System.err.println("SQLException:");
			while(e!=null) {
				System.err.println ("Error msg: " + e.getMessage());
				System.err.println ("SQLSTATE: " + e.getSQLState());
				System.err.println ("Error code: " + e.getErrorCode());
				e.printStackTrace();
				//if(e.getErrorCode() == -4470 )
				//FIXME I think I should restart db2 driver?
				e = e.getNextException();
			}				
		} catch (Exception e) {			
			e.printStackTrace();			
		} 
		return attr;
	}

	public void setRole(String role) {
		// TODO Auto-generated method stub
		
	}

	public void resetRole() {
		// TODO Auto-generated method stub
		
	}
}
